###########################################
# 
###########################################

#!/usr/bin/env python3
# coding: utf-8
"""
2025-04-20 卡住查询
"""

from PySide6.QtCore import Slot, QSize
from PySide6.QtGui import QAction, QKeySequence,QIntValidator, QTextCursor   
from PySide6.QtWidgets import QMainWindow, QApplication, QVBoxLayout, QHBoxLayout, QPushButton, QWidget, QMessageBox, QLineEdit, QTableWidget, QTableWidgetItem, QHeaderView, QAbstractItemView, QLabel,QCheckBox,QTextEdit,QGroupBox
from PySide6.QtCore import Qt, QModelIndex,QMetaObject,Q_ARG
from PySide6.QtGui import QGuiApplication, QCloseEvent
## from PySide6.QtCore.Qt import 


##from PySide6.QtCore import WindowState

## from top.wardyan.core.utils import winformutils
from top.wardyan.core.action.baseaction import WMainWindow,WQdialog
from top.wardyan.core.vo.vos import *
from top.wardyan.core.widges.tables import WTableView

##from warningmain import *
from top.wardyan.core.wdb.core.DBPool import POOL

from top.wardyan.core.vo.vos import *
from top.wardyan.core.wdb.core.db import *
from config import Config
from top.wardyan.core.sqlite import sqliteconfigs

import threading
import time
import re
## cn = rpyc.connect("127.0.0.1", 8899)
# print("time is {}".format(cn.root.get_time()))
# print(cn.root.script_list())
import sys

from apscheduler.schedulers.background import BackgroundScheduler
from top.wardyan.core.vo.mqtt import MqttMsg
from top.wardyan.core.mqtt import mqttconfig
from top.wardyan.core.erp.erp import *
from top.wardyan.core.utils.common import *

import uuid








## 超过这个时间就自动强制kill掉 单位毫秒, 默认1小时
MAXCOST = Config.getconfig_int("autokill","maxcost")
AUTOKILL = Config.getconfig_bool("autokill","autokill")
AUTOREFRESH = Config.getconfig_int("autokill","autorefresh")
LISTTYPE = Config.getconfig_int("autokill","listtype")

if AUTOKILL is None : 
    AUTOKILL = False
if AUTOREFRESH is None : 
    AUTOREFRESH = 60
if LISTTYPE is None :
    LISTTYPE = 0

# maxcost_idle=3600000
# # 闲时段, 在闲时段的超时时长为maxcost_idle,多个小时用逗号隔开,24小时制
# idlehour=22,23,0,1,2,3,4,5,6
MAXCOST_IDLE = Config.getconfig_int("autokill","maxcost_idle")
if MAXCOST_IDLE is None : 
    MAXCOST_IDLE = 3600000
_IDLEHOUR_ = Config.getconfig_str("autokill","idlehour")
IDLEHOURS = []
if _IDLEHOUR_ is not None :
    ## IDLEHOURS = _IDLEHOUR_.split(",")
    for i in _IDLEHOUR_.split(",") :
        IDLEHOURS.append(int(i))

SQL_FINDMAXCOST="""
SELECT *
FROM (
    SELECT  MAX(CASE WHEN er.start_time < DATEADD(DAY, -1, GETDATE()) THEN 999999999 ELSE DATEDIFF(MILLISECOND, er.start_time, GETDATE()) END) AS 耗时毫秒
    FROM    sys.dm_exec_requests AS  er (NOLOCK)
    WHERE   er.session_id > 50 
    AND er.session_id <> @@SPID
    AND er.sql_handle IS NOT null
) AS a
WHERE a.耗时毫秒 > %d
"""

SQL_LISTALL = """
SELECT  
	MAX(CASE WHEN er.start_time < DATEADD(DAY, -1, GETDATE()) THEN 999999999 ELSE DATEDIFF(MILLISECOND, er.start_time, GETDATE()) END) AS 耗时毫秒
		, COUNT(sp.ecid) AS 线程数
		, MAX(er.percent_complete) AS 完成比例
		, er.session_id AS 线程ID
		, MAX(er.status)  AS 状态
		, CAST(MAX(er.wait_type) AS NVARCHAR(100)) AS 等待
		-- , OBJECT_NAME(qt.objectid) AS xObject			-- 对象
		, MAX(b.name) AS 名称
		, LEFT(MAX(SUBSTRING(qt.text, er.statement_start_offset / 2, ( CASE	WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))* 2 ELSE er.statement_end_offset END  - er.statement_start_offset ) / 2)), 300) AS 正在执行语句
		, LEFT(MAX(qt.text), 300) AS  来源脚本
		, MAX(er.start_time) AS 开始时间
		, MAX(er.blocking_session_id )AS 被这个线程堵塞
		, MAX(er.wait_time) AS 等待时间
		, MAX(er.wait_resource) AS 等待类型
		, MAX(er.reads) AS 物理读行
		, MAX(er.writes) AS 写行
		,MAX(er.logical_reads) AS 逻辑度行
		, MAX(er.row_count) AS 行数
		, MAX(er.cpu_time) AS CPU耗时毫秒
--		 , dp.query_plan AS xQueryPlan		-- 执行计划 可能没有
		-- more
		, MAX(DB_NAME(sp.dbid)) AS 数据库
		, MAX(sp.loginame) AS 用户
		, MAX(er.command) AS 命令
		, MAX(sp.program_name) AS 程序
		, MAX(sp.hostname) AS 主机
FROM    sys.dm_exec_requests AS  er (NOLOCK)
INNER JOIN sys.sysprocesses AS  sp (NOLOCK) ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
LEFT JOIN sys.objects AS b (NOLOCK) ON b.object_id = qt.objectid
WHERE   er.session_id > 50 -- Ignore system spids.
AND er.session_id <> @@SPID
GROUP BY er.session_id
having MAX(CASE WHEN er.start_time < DATEADD(DAY, -1, GETDATE()) THEN 999999999 ELSE DATEDIFF(MILLISECOND, er.start_time, GETDATE()) END) > %d
ORDER BY MAX(CASE WHEN er.start_time < DATEADD(DAY, -1, GETDATE()) THEN 999999999 ELSE DATEDIFF(MILLISECOND, er.start_time, GETDATE()) END) desc
"""





## 不需要UI的情况下,定时执行SQL_FINDMAXCOST,如果没有结果则继续休眠,如果有结果则交给完整的查询
def monitor(maxcost : int = MAXCOST, maxcost_idle : int = MAXCOST_IDLE, idlehours : list[int] = IDLEHOURS, autokill : bool = AUTOKILL, listtype : int = LISTTYPE) :
    
    ## 这个只是显示出来的, 万一UI要显示出来
    rs = []
    ## 现在是闲时还是忙时
    currenthour = datetime.datetime.now().hour
    mc = maxcost
    inidle = False
    if currenthour in idlehours :
        mc = maxcost_idle
        inidle = True
    print("{} 开始检查卡住查询,参数: maxcost={}, maxcost_idle={}, idlehours={}, autokill={}, listtype={}, 当前小时:{}, 闲时:{},当前限制最大耗时:{}"
          .format(current_timestamp(), maxcost, maxcost_idle, idlehours, autokill, listtype, currenthour, inidle, mc))
    ## param = tuple([mc])
    param = (mc, )
    data =  find(SQL_FINDMAXCOST, param)
    if data is not None and len(data) > 0 :
        rs : list[Record] = query(SQL_LISTALL, param)
        ## 判断是否有问题, 是否需要KILL, 是否需要上报
        rs = listtorecords(rs)
        if rs is not None and len(rs) > 0 :
            ## 超时的全部需要上报
            ## 上报时要压缩
            ## 另开一个线程来发送这个数据
            sss = json.dumps([r.tojsonstr() for r in rs], ensure_ascii=False)
            ## 在这里能查出来的都是要被KILL的了 所以直接说业务类型是11102
            mqttmsg = MqttMsg(message=sss, business=11102)
            mqttconfig.send(mqttconfig.TOPIC, mqttmsg)
            
            for r in rs :
                cost = r.getInt("耗时毫秒")
                id = r.getInt("线程ID")
                objectName = r.getStr("名称")
                ## 被KILL掉的每个都要上报
                if cost > 0 and mc > 0 and cost > mc and autokill :
                    ## 检查白名单黑名单
                    ok = False
                    lists = sqliteconfigs.list_objectlist()
                    for a in lists :
                        if ok :
                            break
                        isre = int(a[2])
                        name = a[1]
                        if listtype == 2 :
                            ## 黑名单模式
                            if isre == 1 :
                                if re.match(name, objectName) :
                                    print("黑名单模式, 正则符合,可以KILL", objectName)
                                    ok = True
                            elif name == objectName :
                                print("黑名单模式, 直接匹配,可以KILL")
                                ok = True
                            else :
                                print("黑名单模式, 不匹配,不能KILL,直接退出", objectName)
                                ok = False
                        elif listtype == 1 :
                            if isre == 1 :
                                if re.match(name, objectName) :
                                    print("白名单模式, 正则符合,不能KILL", objectName)
                                    ok = False
                            elif name == objectName :
                                print("白名单模式, 白名单直接匹配,不能KILL", objectName)
                                ok = False
                            else :
                                print("白名单模式, 白名单不匹配,能KILL", objectName)
                                ok = True
                        else :
                            print("默认模式,不启用白名单或者黑名单,只要符合条件就KILL:", objectName)
                            ok = True
                    if ok :
                        exec("commit tran;  KILL " + str(id) + "; begin tran;")
                        s = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + "已KILL线程ID:" + str(id) + ",对象名:" + objectName
                        print(s)



if __name__ == "__main__" :
    scheduler = BackgroundScheduler()
    scheduler.add_job(monitor, 'interval', seconds=6)
    scheduler.start()
    while True :
        time.sleep(1)


